﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using EHR.Ctrls.QueryDesign;
using System.Xml.Linq;
using System.Data;
using EHR.DataModel;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using System.IO;
using CommunitySystem.BO.自定义查询;
using CommunitySystem.BO;

namespace CommunitySystem.Web.Service
{
    // NOTE: You can use the "Rename" command on the "Refactor" menu to change the class name "DataQuery" in code, svc and config file together.
    public class DataQuery : IDataQuery
    {
        DataQueryBo bo = new DataQueryBo();
        public IList<ViewInfo> GetQueryTables()
        {

            return bo.GetDataQuery();
        }

        public IList<ViewInfo> GetQueryViews()
        {
            return bo.GetQueryViewsQuery();
        }

        public string GetViewRecords(string xml)
        {
            return CustomerQuery(xml);
        }

        public void CreateView(ViewInfo vi)
        {
            vi.CreateTime = DateTime.Now;
            //DataAccessor.Insert("InsertViewInfo", vi);
            bo.InserQuery(vi);
        }

        public void UpdateView(ViewInfo vi)
        {
            //DataAccessor.Update("UpdateViewInfo", vi);
            bo.UpdateView(vi);
        }


        public void DeleteView(Guid id)
        {
            //DataAccessor.Delete("DeleteViewInfo", id);
            bo.DeleteView(id);
        }

        public int GetEmpIdByEmpNO(string EmpNO)
        {
            //var _TempTable = DataAccessor.ExeQuery("select ID from empbasic where EmployeeNo='" + EmpNO + "'");
            //if (_TempTable != null && _TempTable.Rows.Count > 0)
            //{
            //    int _EmpID = -1;
            //    int.TryParse(_TempTable.Rows[0][0].ToString(), out _EmpID);
            //    return _EmpID;                
            //}
            return -1;
        }

        #region 自定义查询
        public static string CustomerQuery(string xml)
        {
            IDictionary<string, FieldDefine> fDic = new Dictionary<string, FieldDefine>();

            string sql = BuildSql(xml, fDic);

            DataTable table = AccessHelper.GetInstance().ExcuteDataSet(sql).Tables[0];

            return DataTableToXml(table, fDic);
        }

        private static string DataTableToXml(DataTable table, IDictionary<string, FieldDefine> fDic)
        {
            XElement xCols = new XElement("Columns");
            XElement xRows = new XElement("Rows");
            XElement xRoot = new XElement("Table",
                xCols,
                xRows);

            foreach (DataColumn col in table.Columns)
            {
                XElement xCol = new XElement("Column",
                    new XAttribute("ColumnName", col.ColumnName),
                    new XAttribute("ColumnType", (int)fDic[col.ColumnName].Type));
                xCols.Add(xCol);
            }

            foreach (DataRow row in table.Rows)
            {
                XElement xRow = new XElement("Row");
                foreach (DataColumn c in table.Columns)
                {
                    XAttribute xAtt = new XAttribute(c.ColumnName, row[c].ToString());
                    xRow.Add(xAtt);
                }
                xRows.Add(xRow);
            }

            return xRoot.ToString();
        }

        private static string BuildSql(string xml, IDictionary<string, FieldDefine> fDic)
        {
            XElement el = XElement.Parse(xml);
            string tableName = el.Attribute("TableName").Value;
            StringBuilder sb = new StringBuilder();
            sb.Append("select ");
            bool firstCol = true;
            var cols = el.Element("Columns").Elements();
            foreach (XElement xCol in cols)
            {
                string fieldName = xCol.Attribute("FieldName").Value;
                FieldType fieldType = (FieldType)int.Parse(xCol.Attribute("FieldType").Value);//类型
                bool allowNull = bool.Parse(xCol.Attribute("AllowNull").Value);//允许空
                fDic.Add(fieldName, new FieldDefine(allowNull, fieldType));

                if (bool.Parse(xCol.Attribute("IsVisible").Value))
                {
                    if (!firstCol)
                    {
                        sb.Append(", ");
                    }
                    firstCol = false;
                    sb.Append(fieldName);
                }
            }
            sb.AppendFormat(" from {0}", tableName);

            AppendWhere(sb, el, fDic);

            AppendOrderBy(sb, el);

            return sb.ToString();
        }

        private static void AppendWhere(StringBuilder sb, XElement el, IDictionary<string, FieldDefine> fDic)
        {
            var filters = el.Element("Filters").Elements();
            bool firstFilter = true;
            bool where = true;
            foreach (XElement xFilter in filters)
            {
                if (where)
                {
                    sb.Append(" where ");
                }
                where = false;

                if (!firstFilter)
                {
                    bool isAnd = bool.Parse(xFilter.Attribute("IsAnd").Value);
                    string logic = isAnd ? "and" : "or";
                    sb.AppendFormat(" {0} ", logic);
                }
                firstFilter = false;

                string fieldName = xFilter.Attribute("FieldName").Value;
                FilterOp op = (FilterOp)int.Parse(xFilter.Attribute("Op").Value);
                string filterValue = xFilter.Attribute("Value").Value;

                bool allowNull = fDic[fieldName].AllowNull;
                FieldType fieldType = fDic[fieldName].Type;
                if (allowNull && string.IsNullOrWhiteSpace(filterValue))//null情况
                {
                    if (op == FilterOp.等于)
                    {
                        string nullFmt = fieldType == FieldType.String ? "({0} is null or {0} = '')" : "{0} is null";
                        sb.AppendFormat(nullFmt, fieldName);
                    }
                    else if (op == FilterOp.不等于)
                    {
                        string nullFmt = fieldType == FieldType.String ? "({0} is not null or {0} != '')" : "{0} is not null";
                        sb.AppendFormat(nullFmt, fieldName);
                    }
                    else
                    {
                        if (fieldType != FieldType.String)
                        {
                            throw new Exception("null: 非string类型的，只能有'='和'!='操作");
                        }
                    }
                }
                else//非null情况
                {
                    string filterFmt = BuildOpFormat(op);
                    switch (fieldType)
                    {
                        case FieldType.DateTime:
                            sb.AppendFormat(filterFmt, fieldName, "'" + filterValue + "'");
                            break;
                        case FieldType.String:
                            if (op == FilterOp.包含 || op == FilterOp.始于)
                            {
                                sb.AppendFormat(filterFmt, fieldName, filterValue);
                            }
                            else
                            {
                                sb.AppendFormat(filterFmt, fieldName, "'" + filterValue + "'");
                            }
                            break;
                        case FieldType.Bool://filterValue为: 0 / 1
                            sb.AppendFormat(filterFmt, fieldName, filterValue);
                            break;
                        default:
                            sb.AppendFormat(filterFmt, fieldName, filterValue);
                            break;
                    }
                }

                firstFilter = false;
            }
        }

        private static void AppendOrderBy(StringBuilder sb, XElement el)
        {
            var orderBys = el.Element("OrderBys").Elements();
            bool firstLoop = true;
            foreach (XElement xOrd in orderBys)
            {
                string field = xOrd.Attribute("FieldName").Value;
                bool bIsAsc = bool.Parse(xOrd.Attribute("IsAsc").Value);
                string asc = bIsAsc ? "asc" : "desc";
                if (firstLoop)
                {
                    sb.AppendFormat(" order by {0} {1}", field, asc);
                    firstLoop = false;
                }
                else
                {
                    sb.AppendFormat(", {0} {1}", field, asc);
                }
            }
        }

        private static string BuildOpFormat(FilterOp op)
        {
            switch (op)
            {
                case FilterOp.等于:
                    return "{0} = {1}";
                case FilterOp.不等于:
                    return "{0} != {1}";
                case FilterOp.大于:
                    return "{0} > {1}";
                case FilterOp.大于等于:
                    return "{0} >= {1}";
                case FilterOp.小于:
                    return "{0} < {1}";
                case FilterOp.小于等于:
                    return "{0} <= {1}";
                case FilterOp.始于:
                    return "{0} like '{1}%'";
                case FilterOp.包含:
                    return "{0} like '%{1}%'";
                default:
                    throw new Exception("出现了其他比较操作，可能是增加了新的枚举项！");
            }
        }


        #region 将Table数据导出到Excel
        public byte[] TableExportExcel(string xmlTable)
        {
            XElement xTable = XElement.Parse(xmlTable);
            IEnumerable<XElement> xCols = xTable.Element("Columns").Elements();
            IEnumerable<XElement> xRows = xTable.Element("Rows").Elements();
            HSSFWorkbook workbook = BuildTableCells(xTable, xCols, xRows);
            return ExcelToBytes(workbook);
        }

        private byte[] ExcelToBytes(HSSFWorkbook workbook)
        {
            byte[] buffer;
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                buffer = ms.ToArray();
                ms.Close();
            }

            return buffer;
        }
        private HSSFWorkbook BuildTableCells(XElement xTable, IEnumerable<XElement> xCols, IEnumerable<XElement> xRows)
        {
            int colCount = xCols.Count();
            int rowCount = xRows.Count();
            // 一个工作薄
            HSSFWorkbook workbook = new HSSFWorkbook();
            // 一个表格
            HSSFSheet sheet = workbook.CreateSheet("Sheet1");
            // 设置表格默认列宽度为15个字节
            sheet.DefaultColumnWidth = (short)7;
            // 一个标题样式
            HSSFCellStyle titleStyle = BuildTitleCellStyle(workbook);
            HSSFCell headCell = sheet.CreateRow(0).CreateCell(0);
            headCell.CellStyle = titleStyle;
            headCell.SetCellValue(xTable.Attribute("Name").Value);
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, colCount + 1));
            sheet.GetRow(0).Height = 40 * 20;
            //
            HSSFCellStyle cellStyle = BuildNormalCellStyle(workbook);
            int cIndex = 1;
            foreach (var xCol in xCols)
            {
                HSSFCell cell = sheet.CreateRow(1).CreateCell(cIndex);
                cell.CellStyle = cellStyle;
                cell.SetCellValue(xCol.Attribute("DisplayName").Value);
                sheet.SetColumnWidth(cIndex, 50 * int.Parse(xCol.Attribute("Width").Value));
                cIndex++;
            }
            //
            int rIndex = 2;
            foreach (var xRow in xRows)
            {
                cIndex = 1;
                foreach (var xCol in xCols)
                {
                    HSSFCell cell = sheet.CreateRow(rIndex).CreateCell(cIndex);
                    cell.SetCellValue(xRow.Attribute(xCol.Attribute("Name").Value).Value);
                    cell.CellStyle = cellStyle;
                    cIndex++;
                }
                rIndex++;
            }

            return workbook;
        }

        private HSSFCellStyle BuildTitleCellStyle(HSSFWorkbook workbook)
        {
            // 生成一个样式
            HSSFCellStyle style = workbook.CreateCellStyle();
            // 设置这些样式
            style.Alignment = HSSFCellStyle.ALIGN_CENTER;
            style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
            // 生成一个字体
            HSSFFont font = workbook.CreateFont();
            font.FontHeightInPoints = (short)20;
            font.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
            // 把字体应用到当前的样式
            style.SetFont(font);
            return style;
        }

        private HSSFCellStyle BuildNormalCellStyle(HSSFWorkbook workbook)
        {
            HSSFCellStyle style = workbook.CreateCellStyle();
            style.BorderLeft = HSSFCellStyle.BORDER_THIN;
            style.BorderTop = HSSFCellStyle.BORDER_THIN;
            style.BorderRight = HSSFCellStyle.BORDER_THIN;
            style.BorderBottom = HSSFCellStyle.BORDER_THIN;

            style.Alignment = HSSFCellStyle.ALIGN_CENTER;
            style.VerticalAlignment = HSSFCellStyle.VERTICAL_CENTER;
            style.WrapText = true;
            return style;
        }
        #endregion
        #endregion
    }

    public class FieldDefine
    {
        public bool AllowNull { get; set; }
        public FieldType Type { get; set; }

        public FieldDefine(bool allowNull, FieldType type)
        {
            AllowNull = allowNull;
            Type = type;
        }
    }
}
